package com.lmf.TravelData.util;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;


import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

public class DistinctData {

	static HashMap<String, Travel_Data> travelMap = new HashMap();

	public static void main(String[] args) {
		// 读取文本文件内容放入HashMap里面去重(key:title,value:数据对象)
		String filePath = "";   // 允许文件内指定文件路径，或使用文件路径作为命令参数(默认优先读取)D:\\用户目录\\Documents\\笔试题\\travel_data.csv
		if(args.length != 0) {
			filePath = args[0];
		}
		System.out.println(filePath);
		readFileContent(filePath);
		
		// 将数据放入mysql,并执行存储过程匹配各城市的新闻
		try {
			// 将数据放入mysql
			insertTravelData();
			
			// 执行存储过程
			callProcedure();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// 清空数据
		travelMap.clear(); 
	}
	
	// 读取文本文件内容放入HashMap里面去重
	public static void readFileContent(String fileName) {
	    File file = new File(fileName);
	    BufferedReader reader = null;
	    StringBuffer sbf = new StringBuffer();
	    try {
	        //reader = new BufferedReader(new FileReader(file));
	    	reader = new BufferedReader(new InputStreamReader(new DataInputStream(new FileInputStream(file)), "GBK"));
	        String tempStr;
	        reader.readLine();    // 不读取第一行
	        while ((tempStr = reader.readLine()) != null) {

	        	String[] datas = tempStr.split(",");    // csv文件以逗号（,）分隔
	        	if(!travelMap.containsKey(datas[0])) {  // 若有重复title,则忽略，不添加
	        		travelMap.put(datas[0], new Travel_Data(datas[0], datas[1], datas[2], datas[3], datas[4], datas[5]));
	        	}
	        }
	        reader.close();
	    } catch (IOException e) {
	        e.printStackTrace();
	    } finally {
	        if (reader != null) {
	            try {
	                reader.close();
	            } catch (IOException e1) {
	            }
	        }
	    }
	}
	
	public static void insertTravelData() {
		con = getConnection();
		PreparedStatement pstm = null;
		try {
			con.setAutoCommit(false);
			pstm = con.prepareStatement("insert into travel_data(article_title, "
					+ "article_summary, article_time, article_from, label, web_from) values(?,?,?,?,?,?)");
			Collection<Travel_Data> clt = travelMap.values();
	        for (Travel_Data travel : clt) {
	        	pstm.setString(1, travel.getTitle());
	        	pstm.setString(2, travel.getSummary());
	        	pstm.setString(3, travel.getTime());
	        	pstm.setString(4, travel.getSource());
	        	pstm.setString(5, travel.getLabel());
	        	pstm.setString(6, travel.getSiteName());        	
	        	pstm.addBatch();
	        }
	        pstm.executeBatch();
	        con.commit();
	        System.out.println("数据已添加到数据库！");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("请检查数据，有可能是主键数据冲突！");
		}finally {
			try {
				if(pstm != null) 
					pstm.close();
				if(con != null)
					con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		}
	}
	
	public static void callProcedure() {
		con = getConnection();
		CallableStatement cstm = null;
		try {
			cstm = con.prepareCall("{call travel_classify()}");
			cstm.execute();
			System.out.println("已执行存储过程归类！");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("请检查存储过程！");
		}finally {
			try {
				if(cstm != null) 
					cstm.close();
				if(con != null)
					con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		}
	}
	
    static Connection con;
    public static Connection getConnection() {
    	Map configMap = initConfig();
        try {
        	
            Class.forName(configMap.get("jdbc.driverClassName").toString());
            System.out.println("数据库驱动加载成功");
        } catch(ClassNotFoundException e){
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(configMap.get("jdbc.url").toString(),
            		configMap.get("jdbc.username").toString(),configMap.get("jdbc.password").toString());   // mysql连接
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }
	
	public static Map initConfig(){
		try {
			HashMap dMap = new HashMap();
			Properties props = new Properties();
			Resource resource = new ClassPathResource("jdbc.properties");
			props.load(resource.getInputStream());
            //props.load(new FileInputStream("src/main/resources/jdbc.properties"));
            dMap.put("jdbc.driverClassName", props.getProperty("jdbc.driverClassName"));
            dMap.put("jdbc.url", props.getProperty("jdbc.url"));
            dMap.put("jdbc.database", props.getProperty("jdbc.database"));
            dMap.put("jdbc.username", props.getProperty("jdbc.username"));
            dMap.put("jdbc.password", props.getProperty("jdbc.password"));
            
            return dMap;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	static class Travel_Data{
		private String title;
		private String summary;
		private String time;
		private String source;
		private String label;
		private String siteName;
		public String getTitle() {
			return title;
		}
		public void setTitle(String title) {
			this.title = title;
		}
		public String getSummary() {
			return summary;
		}
		public void setSummary(String summary) {
			this.summary = summary;
		}
		public String getTime() {
			return time;
		}
		public void setTime(String time) {
			this.time = time;
		}
		public String getSource() {
			return source;
		}
		public void setSource(String source) {
			this.source = source;
		}
		public String getLabel() {
			return label;
		}
		public void setLabel(String label) {
			this.label = label;
		}
		public String getSiteName() {
			return siteName;
		}
		public void setSiteName(String siteName) {
			this.siteName = siteName;
		}
		public Travel_Data(String title, String summary, String time, String source, String label, String siteName) {
			super();
			this.title = title;
			this.summary = summary;
			this.time = time;
			this.source = source;
			this.label = label;
			this.siteName = siteName;
		}
		public Travel_Data() {
			super();
		}
		
	}
	
}
